package org.jivesoftware.openfire.plugin.chatlogs;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang.StringUtils;
import org.jivesoftware.database.DbConnectionManager;
import org.jivesoftware.openfire.plugin.common.ResponseMessage;
import org.jivesoftware.openfire.plugin.common.Result;
import org.jivesoftware.openfire.plugin.model.ChatLogs;
import org.jivesoftware.openfire.plugin.model.Group;
import org.jivesoftware.openfire.plugin.model.GroupChatLogs;
import org.jivesoftware.openfire.plugin.model.User;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * 聊天记录DB操作类
 */
public class DbChatLogsManager {

    private static final Logger Log = LoggerFactory.getLogger(DbChatLogsManager.class);

    private static final DbChatLogsManager CHAT_LOGS_MANAGER = new DbChatLogsManager();

    private DbChatLogsManager() {
    }

    public static DbChatLogsManager getInstance() {
        return CHAT_LOGS_MANAGER;
    }

    private static final String LOGS_COUNT = "SELECT count(1) FROM ofchatlogs";
    private static final String LOGS_SEARCH = "SELECT * FROM ofchatlogs where state = 0";

    /**
     * 获取最后一个ID
     */
    private static final String LOGS_LAST_MESSAGE_ID = "SELECT max(messageId) FROM ofchatlogs";

    /**
     * 通过ID查询某条聊天记录详情
     */
    private static final String LOGS_FIND_BY_ID = "SELECT messageId, sessionJid, sender, receiver, createDate, length, content FROM ofchatlogs where messageId = ?";

    /**
     * 删除聊天记录
     */
    private static final String LOGS_REMOVE = "UPDATE ofchatlogs set state = 1 where messageId = ?";

    /**
     * 持久化聊天记录
     */
    private static final String LOGS_INSERT = "INSERT INTO ofchatlogs(messageId, sessionJid, sender, receiver, createDate, length, content, detail, state, uuid, type) VALUES(?,?,?,?,?,?,?,?,?,?,?)";

    /**
     * 查询所有聊天记录(单人)
     */
    private static final String LOGS_QUERY = "SELECT messageId, sessionJid, sender, receiver, createDate, length, content FROM ofchatlogs where state = 0 AND type = 0";

    /**
     * 群聊聊天记录
     */
    private static final String GROUP_CHAT_RECORDS = "SELECT messageId, sessionJid, sender, createDate, uuid, content"
            + ",(select nickname from ofmucnickname where userjid = cl.sender) nickName FROM ofchatlogs cl where state = 0 AND type = 1 AND receiver = ?";

    /**
     * 单聊会话列表
     */
    private static final String SINGLE_CHAT_SESSION_LIST =
            "SELECT * FROM ( SELECT * FROM("
                    + " SELECT messageId, receiver, content, createDate FROM ofchatlogs where state = 0 AND type = 0 AND sender = ?"
                    + " UNION ALL"
                    + " SELECT messageId, sender receiver, content, createDate FROM ofchatlogs WHERE state = 0 AND type = 0 AND receiver = ?) temp1 ORDER BY messageId DESC) temp2"
                    + " GROUP BY receiver ORDER BY messageId DESC";

    /**
     * 群聊会话列表
     */
    private static final String GROUP_CHAT_SESSION_LIST =
            "SELECT receiver, msgId FROM ("
                    + " SELECT oc.receiver, (select messageId from ofchatlogs where receiver = oc.receiver ORDER BY messageId DESC LIMIT 1) msgId"
                    + " FROM ofchatlogs oc INNER JOIN ofmucroom mr ON oc.receiver = mr.name"
                    + " INNER JOIN ofmucaffiliation ff ON ff.roomID = mr.roomID AND ff.jid LIKE ?"
                    + " WHERE oc.state = 0 AND oc.type = 1 AND oc.sender = ? GROUP BY receiver"
                    + " UNION ALL"
                    + " SELECT oc.receiver, (select messageId from ofchatlogs where receiver = oc.receiver ORDER BY messageId DESC LIMIT 1) msgId"
                    + " FROM ofchatlogs oc INNER JOIN ofmucroom mr ON oc.receiver = mr.name"
                    + " INNER JOIN ofmucmember mm ON mr.roomID = mm.roomID AND mm.jid LIKE ?"
                    + " WHERE oc.state = 0 AND oc.type = 1 AND oc.sender = ? GROUP BY receiver"
                    + " )temp ORDER BY temp.msgId DESC";
    /**
     * 用户最近的单聊聊天记录
     */
    private static final String LOGS_USER_CHAT_LOGS_JSON = "SELECT messageId, sessionJid, sender, receiver, createDate, length, uuid, content FROM(" +
            " SELECT messageId, sessionJid, sender, receiver, createDate, length, content, uuid FROM ofchatlogs where state = 0 AND type = 0 and sender = ? AND receiver = ?" +
            " UNION ALL" +
            " SELECT messageId, sessionJid, sender, receiver, createDate, length, content, uuid FROM ofchatlogs WHERE state = 0 AND type = 0 AND sender = ? AND receiver = ?" +
            " )temp" +
            " WHERE 1=1";

    /**
     * 最近的群聊聊天记录
     */
    private static final String LOGS_USER_GROUP_CHAT_LOGS_JSON = "SELECT messageId, sessionJid, sender, receiver, createDate, length, uuid, content FROM(" +
            " SELECT * FROM ofchatlogs WHERE state = 0 AND type = 1 AND receiver = ?" +
            " )temp" +
            " WHERE 1=1";

    /**
     * 获取最后一个id
     */
    public int getLastId() {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = -1;
        try {
            con = DbConnectionManager.getConnection();
            ps = con.prepareStatement(LOGS_LAST_MESSAGE_ID);
            rs = ps.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            } else {
                count = 0;
            }
        } catch (SQLException e) {
            Log.error(e.getMessage(), e);
            return 0;
        } finally {
            DbConnectionManager.closeConnection(ps, con);
        }
        return count;
    }

    /**
     * 获取总数量
     */
    public int getCount() {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = -1;
        try {
            con = DbConnectionManager.getConnection();
            ps = con.prepareStatement(LOGS_COUNT);
            rs = ps.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            } else {
                count = 0;
            }
        } catch (SQLException e) {
            Log.error(e.getMessage(), e);
            return 0;
        } finally {
            DbConnectionManager.closeConnection(ps, con);
        }
        return count;
    }

    /**
     * 删除聊天记录信息
     */
    public boolean remove(Integer id) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = DbConnectionManager.getConnection();
            ps = con.prepareStatement(LOGS_REMOVE);
            ps.setInt(1, id);
            return ps.execute();

        } catch (SQLException e) {
            Log.error("@@@@@@@@@@@ chatLogs remove exception: {}", e);
            return false;
        } finally {
            DbConnectionManager.closeConnection(ps, con);
        }
    }

    public boolean handle(String value) {
        ChatLogs logs = JSONObject.parseObject(value, ChatLogs.class);
        Log.info("@@@@@@@@@@@ kafka insert chatlogs: " + JSON.toJSONString(logs));
        return add(logs);
    }

    /**
     * <b>function:</b> 添加聊天记录信息
     *
     * @param logs YouXinChatLogsPlugin 聊天记录对象
     * @return 是否添加成功
     * @author hoojo
     * @createDate 2012-9-19 下午08:37:52
     */
    public boolean add(ChatLogs logs) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = DbConnectionManager.getConnection();
            ps = con.prepareStatement(LOGS_INSERT);
            int i = 1;
            ps.setLong(i++, logs.getMessageId());
            ps.setString(i++, logs.getSessionJid());
            ps.setString(i++, logs.getSender());
            ps.setString(i++, logs.getReceiver());
//            ps.setString(i++, logs.getCreateDate());
            ps.setTimestamp(i++, logs.getCreateDate());
            ps.setInt(i++, logs.getLength());
            ps.setString(i++, logs.getContent());
            ps.setString(i++, logs.getDetail());
            ps.setInt(i++, logs.getState());
            ps.setString(i++, logs.getUuid());
            ps.setInt(i++, logs.getType());
            return ps.execute();
        } catch (SQLException e) {
            Log.error("@@@@@@@@@@ chatLogs add exception: {}", e);
            return false;
        } finally {
            DbConnectionManager.closeConnection(ps, con);
        }
    }

    /**
     * 通过id查询聊天记录信息
     */
    public ChatLogs find(int id) {
        Connection con = null;
        PreparedStatement ps = null;
        ChatLogs logs = null;

        try {
            con = DbConnectionManager.getConnection();
            ps = con.prepareStatement(LOGS_FIND_BY_ID);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                logs = new ChatLogs();
                logs.setMessageId(rs.getInt("messageId"));
                logs.setContent(rs.getString("content"));
//                logs.setCreateDate(rs.getString("createDate"));
                logs.setCreateDate(rs.getTimestamp("createDate"));
                logs.setLength(rs.getInt("length"));
                logs.setSessionJid(rs.getString("sessionJid"));
                logs.setSender(rs.getString("sender"));
                logs.setReceiver(rs.getString("receiver"));
            }
            return logs;
        } catch (SQLException e) {
            Log.error("@@@@@@@@@@@ chatLogs find exception: {}", e);
            return logs;
        } finally {
            DbConnectionManager.closeConnection(ps, con);
        }
    }

    /**
     * 查询单聊聊天记录
     */
    public Result<?> queryLogs(ChatLogs entity, int pageSize) {
        List<ChatLogs> list = queryUserChatLogs(new ChatLogs(entity.getSender(), entity.getReceiver(), entity.getUuid()), pageSize);
        if (list == null || list.size() == 0) {
            return ResponseMessage.SUCCESS_NOT_FOUND.messageToResult();
        }
        return ResponseMessage.SUCCESS.messageToResult(list);
    }

    /**
     * 查询群聊聊天记录
     */
    public Result<?> queryGroupLogs(ChatLogs entity, int pageSize) {
        List<ChatLogs> list = queryGroupChatLogs(new ChatLogs(entity.getReceiver(), entity.getUuid()), pageSize);
        if (list == null || list.size() == 0) {
            return ResponseMessage.SUCCESS_NOT_FOUND.messageToResult();
        }
        // flag 标志  无值代表要调用社区接口
        if (StringUtils.isEmpty(entity.getFlag())){
            String data = HttpUtils.send(HttpUtils.GROUP_LOG_URL, JSON.toJSONString(list));
            list = JSONArray.parseArray(data,ChatLogs.class);
        }
        return ResponseMessage.SUCCESS.messageToResult(list);
    }

    //单聊聊天记录
    public List<ChatLogs> queryUserChatLogs(ChatLogs entity, int pageSize) {
        Connection con = null;
        PreparedStatement ps = null;
        List<ChatLogs> result = new ArrayList<>();
        try {
            con = DbConnectionManager.getConnection();
            StringBuilder builder = new StringBuilder(LOGS_USER_CHAT_LOGS_JSON);
            if (entity.getUuid() != null && !entity.getUuid().equals("")) {
                builder.append(" AND temp.messageId < (SELECT ol.messageId FROM ofchatlogs ol WHERE ol.uuid = ?)");
            }
            builder.append(" GROUP BY messageId ORDER BY temp.messageId DESC LIMIT 0, ?");
            String sql = builder.toString();
            ps = con.prepareStatement(sql);
            ps.setString(1, entity.getSender());
            ps.setString(2, entity.getReceiver());
            ps.setString(3, entity.getReceiver());
            ps.setString(4, entity.getSender());
            if (entity.getUuid() != null && !entity.getUuid().equals("")) {
                ps.setString(5, entity.getUuid());
                ps.setInt(6, pageSize);
            } else {
                ps.setInt(5, pageSize);
            }
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ChatLogs chatLogs = new ChatLogs();
                chatLogs.setMessageId(rs.getLong("messageId"));
                chatLogs.setSessionJid(rs.getString("sessionJid"));
                chatLogs.setSender(rs.getString("sender"));
                chatLogs.setReceiver(rs.getString("receiver"));
                chatLogs.setLength(rs.getInt("length"));
                chatLogs.setContent(rs.getString("content"));
                chatLogs.setCreateDate(rs.getTimestamp("createDate"));
                chatLogs.setUuid(rs.getString("uuid"));
                result.add(chatLogs);
            }
            return result;
        } catch (SQLException e) {
            Log.error("@@@@@@@@@@@ queryUserChatLogs find exception: {}", e);
            return result;
        } finally {
            DbConnectionManager.closeConnection(ps, con);
        }
    }

    //群聊聊天记录
    public List<ChatLogs> queryGroupChatLogs(ChatLogs entity, int pageSize) {
        Connection con = null;
        PreparedStatement ps = null;
        List<ChatLogs> result = new ArrayList<>();
        try {
            con = DbConnectionManager.getConnection();
            StringBuilder builder = new StringBuilder(LOGS_USER_GROUP_CHAT_LOGS_JSON);
            if (entity.getUuid() != null && !entity.getUuid().equals("")) {
                builder.append(" AND temp.messageId < (SELECT ol.messageId FROM ofchatlogs ol WHERE ol.uuid = ?)");
            }
            builder.append(" ORDER BY temp.messageId DESC LIMIT 0, ?");
            String sql = builder.toString();
            ps = con.prepareStatement(sql);
            ps.setString(1, entity.getReceiver());
            if (entity.getUuid() != null && !entity.getUuid().equals("")) {
                ps.setString(2, entity.getUuid());
                ps.setInt(3, pageSize);
            } else {
                ps.setInt(2, pageSize);
            }
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ChatLogs chatLogs = new ChatLogs();
                chatLogs.setMessageId(rs.getLong("messageId"));
                chatLogs.setSessionJid(rs.getString("sessionJid"));
                chatLogs.setSender(rs.getString("sender"));
                chatLogs.setReceiver(rs.getString("receiver"));
                chatLogs.setLength(rs.getInt("length"));
                chatLogs.setContent(rs.getString("content"));
                chatLogs.setCreateDate(rs.getTimestamp("createDate"));
                chatLogs.setUuid(rs.getString("uuid"));
                result.add(chatLogs);
            }
            return result;
        } catch (SQLException e) {
            Log.error("@@@@@@@@@@@ queryGroupChatLogs find exception: {}", e);
            return result;
        } finally {
            DbConnectionManager.closeConnection(ps, con);
        }
    }

    /**
     * 群组详情
     */
    public Group queryGroupInfo(String name) {
        Connection con = null;
        PreparedStatement ps = null;
        Group group = null;
        try {
            con = DbConnectionManager.getConnection();
            String sql = "SELECT `roomID`, `name`, `naturalName`, `description` FROM ofmucroom WHERE `name` = ? LIMIT 1";
            ps = con.prepareStatement(sql);
            ps.setString(1, name);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                group = new Group();
                group.setRoomId(rs.getInt("roomID"));
                group.setName(rs.getString("name"));
                group.setNaturalName(rs.getString("naturalName"));
                group.setDescription(rs.getString("description"));
            }
            return group;
        } catch (SQLException e) {
            Log.error("@@@@@@@@@@@ chatLogs search exception: {}", e);
            return group;
        } finally {
            DbConnectionManager.closeConnection(ps, con);
        }
    }

    /**
     * 多条件搜索查询，返回List&lt;YouXinChatLogsPlugin>集合
     */
    public List<ChatLogs> query(ChatLogs entity) {
        Connection con = null;
        Statement ps = null;
        ChatLogs logs = null;
        List<ChatLogs> result = new ArrayList<ChatLogs>();
        try {
            con = DbConnectionManager.getConnection();
            ps = con.createStatement();
            String sql = LOGS_QUERY;
            if (entity != null) {
                if (!StringUtils.isEmpty(entity.getSender()) && !StringUtils.isEmpty(entity.getReceiver())) {
                    sql += " and (sender = '" + entity.getSender() + "' and receiver = '" + entity.getReceiver() + "')";
                    sql += " or (receiver = '" + entity.getSender() + "' and sender = '" + entity.getReceiver() + "')";
                } else {
                    if (!StringUtils.isEmpty(entity.getSender())) {
                        sql += " and sender = '" + entity.getSender() + "'";
                    }
                    if (!StringUtils.isEmpty(entity.getReceiver())) {
                        sql += " and receiver = '" + entity.getReceiver() + "'";
                    }
                }
                if (!StringUtils.isEmpty(entity.getContent())) {
                    sql += " and content like '%" + entity.getContent() + "%'";
                }
            }
            sql += " ORDER BY messageId DESC";
            ResultSet rs = ps.executeQuery(sql);
            while (rs.next()) {
                logs = new ChatLogs();
                logs.setMessageId(rs.getInt("messageId"));
                logs.setContent(rs.getString("content"));
//                logs.setCreateDate(rs.getString("createDate"));
                logs.setCreateDate(rs.getTimestamp("createDate"));
                logs.setLength(rs.getInt("length"));
                logs.setSessionJid(rs.getString("sessionJid"));
                logs.setSender(rs.getString("sender"));
                logs.setReceiver(rs.getString("receiver"));
                result.add(logs);
            }
            return result;
        } catch (SQLException e) {
            Log.error("@@@@@@@@@@@ chatLogs search exception: {}", e);
            return result;
        } finally {
            DbConnectionManager.closeConnection(ps, con);
        }
    }

    /**
     * 单聊会话列表
     */
    public List<User> singleChatSessionList(ChatLogs entity) {
        Connection con = null;
        PreparedStatement ps = null;
        List<User> result = new ArrayList<User>();
        try {
            con = DbConnectionManager.getConnection();
            ps = con.prepareStatement(SINGLE_CHAT_SESSION_LIST);
            ps.setString(1, entity.getSender());
            ps.setString(2, entity.getSender());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setReceiver(rs.getString("receiver"));
                user.setContent(rs.getString("content"));
                user.setCreateDate(rs.getString("createDate"));
                result.add(user);
            }
            return result;
        } catch (SQLException e) {
            Log.error("@@@@@@@@@@@ singleChatSessionList find exception: {}", e);
            return result;
        } finally {
            DbConnectionManager.closeConnection(ps, con);
        }
    }

    /**
     * 群聊聊天记录
     */
    public List<GroupChatLogs> getGroupChatRecords(ChatLogs entity, int pageSize) {
        Connection con = null;
        PreparedStatement ps = null;
        List<GroupChatLogs> result = new ArrayList<GroupChatLogs>();
        try {
            con = DbConnectionManager.getConnection();
            StringBuilder builder = new StringBuilder(GROUP_CHAT_RECORDS);
            if (entity.getUuid() != null && !entity.getUuid().equals("")) {
                builder.append(" AND messageId < (SELECT ol.messageId FROM ofchatlogs ol WHERE ol.uuid = ?)");
            }
            builder.append("ORDER BY messageId DESC LIMIT 0, ?");
            String sql = builder.toString();
            ps = con.prepareStatement(sql);
            ps.setString(1, entity.getReceiver());
            if (entity.getUuid() != null && !entity.getUuid().equals("")) {
                ps.setString(2, entity.getUuid());
                ps.setInt(3, pageSize);
            } else {
                ps.setInt(2, pageSize);
            }
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                GroupChatLogs chatLogs = new GroupChatLogs();
                chatLogs.setMessageId(rs.getLong("messageId"));
                chatLogs.setSessionJid(rs.getString("sessionJid"));
                chatLogs.setSender(rs.getString("sender"));
                chatLogs.setContent(rs.getString("content"));
                chatLogs.setCreateDate(rs.getTimestamp("createDate"));
                chatLogs.setUuid(rs.getString("uuid"));
                chatLogs.setNickName(rs.getString("nickName"));
                result.add(chatLogs);
            }
            return result;
        } catch (SQLException e) {
            Log.error("@@@@@@@@@@@ queryUserChatLogs find exception: {}", e);
            return result;
        } finally {
            DbConnectionManager.closeConnection(ps, con);
        }
    }

    /**
     * 群聊会话列表
     */
    public List<User> groupChatSessionList(ChatLogs entity) {
        Connection con = null;
        PreparedStatement ps = null;
        List<User> result = new ArrayList<User>();
        try {
            con = DbConnectionManager.getConnection();
            ps = con.prepareStatement(GROUP_CHAT_SESSION_LIST);
            ps.setString(1, entity.getSender() + "%");
            ps.setString(2, entity.getSender());
            ps.setString(3, entity.getSender() + "%");
            ps.setString(4, entity.getSender());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setReceiver(rs.getString("receiver"));
                result.add(user);
            }
            return result;
        } catch (SQLException e) {
            Log.error("@@@@@@@@@@@ groupChatSessionList find exception: {}", e);
            return result;
        } finally {
            DbConnectionManager.closeConnection(ps, con);
        }
    }

}
